﻿Imports Conection
Imports System.Data.SqlClient
Public Class PecaDAL
    Dim objconex As New Conexao()
    Public OrdCodigo As Integer
    Public VenCodigo As Integer
    Dim View As String
#Region "Incluir ou Alterar"
    Public Function incluialtera(ByVal pec_codigo As Integer, ByVal pro_codigo As Integer, ByVal clf_codigo As Integer, ByVal fab_codigo As Integer, ByVal sta_codigo As Integer,
                            ByVal cat_codigo As Integer, ByVal pec_codbara As String, ByVal pec_qtdemin As Integer, ByVal pec_qtdemax As Integer, ByVal pec_qtde As Integer,
                            ByVal pec_ipi As Decimal, ByVal pec_subtributaria As Decimal, ByVal pec_original As String, ByVal pec_valorunitario As Decimal,
                            ByVal pec_mcontrib As Decimal, ByVal pec_margem As Decimal, ByVal pec_abreviacao As String, ByVal fab_codigofabricante As String,
                            ByVal pro_descricao As String, ByVal pro_precovenda As Decimal, ByVal pro_precocusto As Decimal, ByVal pro_desconto As Decimal, usuario As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPEstPeca"

            cmd.Parameters.Add(New SqlParameter("@pec_codigo", SqlDbType.Int)).Value = pec_codigo
            cmd.Parameters.Add(New SqlParameter("@pro_codigo", SqlDbType.Int)).Value = pro_codigo
            cmd.Parameters.Add(New SqlParameter("@clf_codigo", SqlDbType.Int)).Value = clf_codigo
            cmd.Parameters.Add(New SqlParameter("@fab_codigo", SqlDbType.Int)).Value = fab_codigo
            cmd.Parameters.Add(New SqlParameter("@sta_codigo", SqlDbType.Int)).Value = sta_codigo
            cmd.Parameters.Add(New SqlParameter("@cat_codigo", SqlDbType.Int)).Value = cat_codigo
            cmd.Parameters.Add(New SqlParameter("@pec_codbarra", SqlDbType.VarChar, 15)).Value = pec_codbara
            cmd.Parameters.Add(New SqlParameter("@pec_qtdemin", SqlDbType.Int)).Value = pec_qtdemin
            cmd.Parameters.Add(New SqlParameter("@pec_qtdemax", SqlDbType.Int)).Value = pec_qtdemax
            cmd.Parameters.Add(New SqlParameter("@pec_qtde", SqlDbType.Int)).Value = pec_qtde
            cmd.Parameters.Add(New SqlParameter("@pec_ipi", SqlDbType.Decimal)).Value = pec_ipi
            cmd.Parameters.Add(New SqlParameter("@pec_subtributaria", SqlDbType.Decimal)).Value = pec_subtributaria
            cmd.Parameters.Add(New SqlParameter("@pec_original", SqlDbType.VarChar)).Value = pec_original
            cmd.Parameters.Add(New SqlParameter("@pec_valorunitario", SqlDbType.Decimal)).Value = pec_valorunitario
            cmd.Parameters.Add(New SqlParameter("@pec_mcontrib", SqlDbType.Decimal)).Value = pec_mcontrib
            cmd.Parameters.Add(New SqlParameter("@pec_margem", SqlDbType.Decimal)).Value = pec_margem
            cmd.Parameters.Add(New SqlParameter("@pec_abreviacao", SqlDbType.VarChar)).Value = pec_abreviacao
            cmd.Parameters.Add(New SqlParameter("@fab_codigofabricante", SqlDbType.VarChar)).Value = fab_codigofabricante
            cmd.Parameters.Add(New SqlParameter("@pro_descricao", SqlDbType.VarChar)).Value = pro_descricao
            cmd.Parameters.Add(New SqlParameter("@pro_precovenda", SqlDbType.Decimal)).Value = pro_precovenda
            cmd.Parameters.Add(New SqlParameter("@pro_precocusto", SqlDbType.Decimal)).Value = pro_precocusto
            cmd.Parameters.Add(New SqlParameter("@pro_desconto", SqlDbType.Decimal)).Value = pro_desconto
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usuario
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Buscar"
    Function buscar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            If OrdCodigo > 0 Then
                cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = OrdCodigo
                View = "(select pro_codigo from vw_VenItensOrdem where ord_codigo=@ordcodigo)"
            Else
                cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = VenCodigo
                View = "(select pro_codigo from vw_VenItensVenda where ven_codigo=@ordcodigo)"
            End If

            Select Case tipo
                Case 1
                    'por codigo
                    cmd.CommandText = "select * from vw_EstPeca where  pec_codigo = @codigo " & _
                        "and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = descricao
                Case 2
                    'por descricao
                    cmd.CommandText = "select [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra,[pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante]," & _
                        "[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pro_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
                Case 3
                    'status
                    cmd.CommandText = "select  [pro_codigo], [pec_codigo],pec_qtdemin, pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where sta_codigo=@codigo" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 4
                    'disponibilidade
                    If (descricao = "D") Then
                        cmd.CommandText = "select [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pec_qtde>0 " & _
                            "and pro_codigo Not in" + View
                    Else
                        cmd.CommandText = "select [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pec_qtde<=0" & _
                            " and pro_codigo Not in" + View
                    End If
                Case 5
                    'codigo de barras
                    cmd.CommandText = "select  [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pec_codbarra=@codigo " & _
                        "and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = descricao

                Case 6
                    'código original
                    cmd.CommandText = "select  [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pec_original=@codigo " & _
                        "and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = descricao
                Case 7
                    'codigo do fabricante
                    cmd.CommandText = "select  [pro_codigo], [pec_codigo], pec_qtdemin, pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca where pec_codigofabricante=@codigo" & _
                        " and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = descricao
                Case 8
                    'buscar por codigo produto
                    cmd.CommandText = "select  * from vw_EstPeca where pro_codigo=@codigo " & _
                        "and pro_codigo Not in" + View
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = descricao

                Case Else
                    'todos
                    cmd.CommandText = "select  [pro_codigo], [pec_codigo], pec_qtdemin,pec_codbarra, [pro_descricao], [pro_precovenda],[pro_desconto],[sta_descricao],[pec_qtde],[pec_original],[pec_codigofabricante],[clf_descricao],[fab_descricao],[cat_descricao] from vw_EstPeca" & _
                        " where pro_codigo Not in" + View

            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab

    End Function
#End Region

#Region "Buscar peca servico"
    Function buscarpecaservico(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.Parameters.Add(New SqlParameter("@ordcodigo", SqlDbType.Int)).Value = OrdCodigo
            Select Case tipo
                Case 1
                    'buscar peça e produto em uma visão unica, utilizado na consulta que tras ambos (utilizado por adriano)
                    cmd.CommandText = "select * from vw_EstPecaProduto"
                Case Else
                    'todos
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab

    End Function
#End Region

#Region "Get codigo Produto"
    Function getproduto(pec_codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer = 0
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select ISNULL(pro_codigo,0) as pro_codigo from vw_EstPeca where pec_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = pec_codigo
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count > 0) Then
                ret = tab.Rows(0)("pro_codigo").ToString
            Else
                ret = 0
            End If
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Entrada Manual"
    Public Function entradamanual(pro_codigo As Integer, operacao As String, qtde As Integer, custo As Decimal, venda As Decimal, mc As Decimal, m As Decimal, usuario As Integer) As Boolean

        Dim cmd As New SqlCommand
        Try
            cmd.CommandText = "execute spEntradamanual @pro_codigo, @qtde,@custo, @venda, @m, @mc, @usu_codigo, @op"
            cmd.Parameters.Add(New SqlParameter("@qtde", SqlDbType.Int)).Value = qtde
            cmd.Parameters.Add(New SqlParameter("@m", SqlDbType.Decimal)).Value = m
            cmd.Parameters.Add(New SqlParameter("@mc", SqlDbType.Decimal)).Value = mc
            cmd.Parameters.Add(New SqlParameter("@pro_codigo", SqlDbType.Int)).Value = pro_codigo
            cmd.Parameters.Add(New SqlParameter("@custo", SqlDbType.Decimal)).Value = custo
            cmd.Parameters.Add(New SqlParameter("@venda", SqlDbType.Decimal)).Value = venda
            cmd.Parameters.Add(New SqlParameter("@op", SqlDbType.VarChar)).Value = operacao
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usuario
            objconexao.executaCmd(cmd)
            Return True
        Catch ex As Exception
            Throw New Exception("Erro ao atualizar estoque.")
        End Try
        Return False
    End Function
#End Region

#Region "Verificar Relacionamentos"
    Public Function verificarelacionamento(codigo As Integer) As String
        Dim cmd As New SqlCommand
        Dim dt As New DataTable
        Dim ret As String
        Try
            ret = ""
            'verificar em peças
            cmd.CommandText = "select * from vendas.Ofi_ItensAgendamento where pro_codigo in(select pro_codigo from vw_EstPeca where sta_codigo in(1,2)) and pro_codigo=" & codigo
            dt = objconexao.executaConsulta(cmd)
            If (dt.Rows.Count > 0) Then
                ret = "Agendamento"
            End If
            'verificar em ordem serviço / venda
            'cmd = New SqlCommand
            'cmd.CommandText = "select * from vw_EstServico where cat_codigo in (select cat_codigo from vw_EstCategoria where sta_codigo in(1,2)) and cat_codigo=" & codigo
            'dt = objconexao.executaConsulta(cmd)
            'If (dt.Rows.Count > 0) Then
            '    If (ret.Length > 0) Then
            '        ret += ", Venda/Orçamento"
            '    Else : ret = "Venda/Orçamento"
            '    End If
            'End If
        Catch ex As Exception
            Throw New Exception("Erro ao realizar consulta.")
        End Try
        Return ret
    End Function
#End Region
End Class
